package com.qdu.service.impl;

import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.qdu.entity.Grade;
import com.qdu.entity.Student;
import com.qdu.mapper.GradeMapper;
import com.qdu.mapper.StudentMapper;
import com.qdu.service.GradeService;

@Service
public class GradeServiceImpl implements GradeService {

	@Autowired
	GradeMapper gradeMapper;

	@Autowired
	StudentMapper studentMapper;
	
	@Override
	public Grade getGradeById(String gradeId) {
		return gradeMapper.getGradeById(gradeId);
	}

	@Override
	public List<Grade> getGradeByStudentId(String srollno) {
		return gradeMapper.getGradeByStudentId(srollno);
	}

	@Override
	public List<Grade> getAllGrade() {
		return gradeMapper.getAllGrade();
	}

	@Override
	public boolean addGrade(Grade grade, String srollno, int cpid, int pass) {
		return gradeMapper.addGrade(grade, srollno, cpid, pass);
	}

	@Override
	public boolean modifyGrade(Grade grade, String srollno, int cpid, int pass) {
		return gradeMapper.modifyGrade(grade, srollno, cpid, pass);
	}

	@Override
	public boolean deleteGradeById(String gradeId) {
		return gradeMapper.deleteGradeById(gradeId);
	}

	@Override
	public List<Grade> getGradeByCpid(int cpid) {
		return gradeMapper.getGradeByCpid(cpid);
	}

	@Override
	public Grade getGradeBySrollnoAndCpid(String srollno, int cpid) {
		return gradeMapper.getGradeBySrollnoAndCpid(srollno, cpid);
	}

	@Override
	public boolean uploadGrade(MultipartFile file) throws Exception {

		// 得到上传到表
		Workbook workbook = WorkbookFactory.create(file.getInputStream());
		// 获取工作表
		Sheet sheet = workbook.getSheet("sheet1");
		// 获取表的总行数
		int num = sheet.getLastRowNum();
		// 总列数
		// int col = sheet.getRow(0).getLastCellNum();

		// 遍历excel每一行
		for (int i = 1; i <= num; i++) {
			Row row = sheet.getRow(i);
			Cell cell1 = row.getCell(0);
			cell1.setCellType(CellType.STRING);
			Cell cell2 = row.getCell(1);
			Cell cell3 = row.getCell(2);

			Grade grade = new Grade();
			int grad = (int) cell3.getNumericCellValue();
			grade.setGrade(grad);
			int pass =  0;
			if(grad>=60) {
				pass=1;
			}else {
				pass=0;
			}
			grade.setPass(pass);
			Grade gra = gradeMapper.getGradeBySrollnoAndCpid(cell1.getStringCellValue(),
					(int) cell2.getNumericCellValue());
			grade.setGradeId(gra.getGradeId());
			
			if (gra == null) {
				gradeMapper.addGrade(grade, cell1.getStringCellValue(), (int) cell2.getNumericCellValue(),
						pass);
			}else {
				gradeMapper.modifyGrade(grade, cell1.getStringCellValue(), (int) cell2.getNumericCellValue(), pass);
			}
			System.out.println(gra);
		}
		return true;
	}

	@Override
	public XSSFWorkbook downloadGradeMould(int bid,int cpid) {
		// 查询要下载的数据
		List<Student> stuList = studentMapper.selectAllStudentByBid(bid);
		// XSSFWorkbook–Excel中的xlsx版本
		XSSFWorkbook wb = new XSSFWorkbook();
		// 创建一张表
		Sheet sheet = wb.createSheet("sheet1");
		// 创建第一行，索引从零开始，第一行一般作为表头
		Row titleRow = sheet.createRow(0);
		// 创建列，索引从零开始
		titleRow.createCell(0).setCellValue("学号");
		titleRow.createCell(1).setCellValue("开课计划号");
		titleRow.createCell(2).setCellValue("成绩");
		int rowNum = 1;
		for (Student student : stuList) {
			// rowNum=1 即 创建第二行，一般是数据的第一行
			Row row = sheet.createRow(rowNum);
			row.createCell(0).setCellValue(student.getSrollno());
			row.createCell(1).setCellValue(cpid);
			rowNum++;
		}
		return wb;
	}

}
